package DBLayer;
import ModelLayer.Employee;
import java.sql.*;
import java.util.ArrayList;

public class DBEmployee {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBEmployee() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Employee getEmployeeByID(int id) {
        return selectSingle("SELECT `id_employee`, `id_profile`, `lastname`, `firstname`, `email`, `passwd`, `active` FROM `ps_employee` WHERE `id_employee` = " + id + " LIMIT 0, 1;");
    }

    public Employee getEmployeeByEmail(String email) {
        return selectSingle("SELECT `id_employee`, `id_profile`, `lastname`, `firstname`, `email`, `passwd`, `active` FROM `ps_employee` WHERE `email` LIKE '" + email + "' LIMIT 0, 1;");
    }

    public ArrayList<Employee> getEmployeeList() {
        return selectMultiple("SELECT `id_employee`, `id_profile`, `lastname`, `firstname`, `email`, `passwd`, `active` FROM `ps_employee` ORDER BY `id_employee` ASC;");
    }

    private Employee selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Employee employee = new Employee();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                employee = buildEmployee(result);
            }
            System.out.println("Fetching Employee Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return employee;
    }

    private ArrayList<Employee> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Employee> employees = new ArrayList<Employee>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Employee employee = new Employee();
                employee = buildEmployee(result);
                employees.add(employee);
            }
            System.out.println("Fetching Employee List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return employees;
    }

    private Employee buildEmployee(ResultSet result) {
        Employee employee = new Employee();
        try {
            employee.setIDEmployee(result.getInt("id_employee"));
            employee.setIDProfile(result.getInt("id_profile"));
            employee.setLastname(result.getString("lastname"));
            employee.setFirstname(result.getString("firstname"));
            employee.setEmail(result.getString("email"));
            employee.setPasswd(result.getString("passwd"));
            if (result.getInt("active") == 1) {
                employee.setActive(true);
            } else {
                employee.setActive(false);
            }

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return employee;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addEmployee(Employee employee) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_employee` (`id_employee`, `id_profile`, `lastname`, `firstname`, `email`, `passwd`, `active`) "
                + "VALUES (" + employee.getIDEmployee() + ", " + employee.getIDProfile() + ", '" + employee.getLastname() + "', '" + employee.getFirstname() + "', '" + employee.getEmail() + "', '" + employee.getPasswd() + "', ";
        if (employee.getActive() == true)       { query += "1);"; }
        else                                    { query += "0);"; }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Employee ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateEmployee(Employee employee, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_employee` SET "
                + "`id_employee`     = "  + employee.getIDEmployee() + ", "
                + "`id_profile`      = "  + employee.getIDProfile() + ", "
                + "`lastname`        = '" + employee.getLastname() + "', "
                + "`firstname`       = '" + employee.getFirstname() + "', "
                + "`email`           = '" + employee.getEmail() + "', "
                + "`passwd`          = '" + employee.getPasswd() + "', ";
        if (employee.getActive() == true)   { query += "`active` = 1 "; }
        else                                { query += "`active` = 0 "; }
	query += "WHERE `id_employee` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = employee.getIDEmployee();
            System.out.println("Updated Employee ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteEmployee(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_employee` WHERE `id_employee` = " + id + " LIMIT 1;");
            System.out.println("Deleted Employee ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
